package dao;

import gui.PublicMethod;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import model.Class_NIIT;
import model.Classroom;
import model.CommonList;
import model.Major;
import model.Student;
import model.Teacher;
import model.Type;

public class DaoImp_Class implements IDao_Class
{
	private Connection conn;
	private DaoMethod method;
	private CommonList common;

	public DaoImp_Class()
	{
		conn = DaoMethod.getConnection();
		method = new DaoMethod();
		common = CommonList.getInstance();
	}

	public List<Class_NIIT> queryAll(int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryByClassRoom(String classroom, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where classroom like '%"
					+ classroom + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where classroom like '%"
					+ classroom + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where classroom like '%"
					+ classroom + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryByDate(String date_start, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where date_start like '%"
					+ date_start + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where date_start like '%"
					+ date_start + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where date_start like '%"
					+ date_start + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryByMajor(String major, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where major like '%"
					+ major + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where major like '%"
					+ major + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where major like '%"
					+ major + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryByTeacher(String teacher, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where teacher like '%"
					+ teacher + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where teacher like '%"
					+ teacher + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where teacher like '%"
					+ teacher + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryByType(String type, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where type like '%"
					+ type + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where type like '%"
					+ type + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where type like '%"
					+ type + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryDescribe(String description, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where description like '%"
					+ description + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where description like '%"
					+ description + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where description like '%"
					+ description + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	public List<Class_NIIT> queryByCurrentStudentCount(int currentStudentCount,
			int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where people like '%"
					+ currentStudentCount + "%' and isend=0";
			break;
		case PublicMethod.out:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where people like '%"
					+ currentStudentCount + "%' and isend=1";
			break;
		case PublicMethod.both:
			exe = "select teacher,classroom,date_start,people,major,type,description,classId,isend from class_view where people like '%"
					+ currentStudentCount + "%'";
			break;
		default:
			break;
		}
		return getClassList(exe);
	}

	private List<Class_NIIT> getClassList(String exe)
	{
		Statement st = null;
		ResultSet rs = null;
		List<Class_NIIT> list = new ArrayList<Class_NIIT>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			if (rs.next())
			{
				do
				{
					Class_NIIT class_NIIT = new Class_NIIT();
					class_NIIT.setTeacher(rs.getString(1));
					class_NIIT.setClassroom(rs.getString(2));
					class_NIIT.setDate_Start(rs.getDate(3));
					class_NIIT.setPeople(rs.getInt(4));
					class_NIIT.setMajor(rs.getString(5));
					class_NIIT.setType(rs.getString(6));
					class_NIIT.setDescription(rs.getString(7));
					class_NIIT.setId(rs.getInt(8));
					class_NIIT.setIsend(rs.getInt(9));
					list.add(class_NIIT);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}

	public int addTeacher(Teacher teacher)
	{
		String exe = "insert into teacher(teacher_name)values('"
				+ teacher.getName() + "')";
		return method.stUpdate(exe);
	}

	public int addClassroom(Classroom classroom)
	{
		String exe = "insert into classroom(classroom_name)values('"
				+ classroom.getName() + "')";
		return method.stUpdate(exe);
	}

	public int addMajor(Major major)
	{
		String exe = "insert into major(major_name)values('" + major.getName()
				+ "')";
		return method.stUpdate(exe);
	}

	public int addType(Type type)
	{
		String exe = "insert into type(type_name)values('" + type.getName()
				+ "')";
		return method.stUpdate(exe);
	}

	public int deleteClassroom(Classroom classroom)
	{
		String exe = "delete from classroom where classroom_name='"
				+ classroom.getName() + "'";
		return method.stUpdate(exe);
	}

	public int deleteMajor(Major major)
	{
		String exe = "delete from major where major_name='" + major.getName()
				+ "'";
		return method.stUpdate(exe);
	}

	public int deleteTeacher(Teacher teacher)
	{
		String exe = "delete from teacher where teacher_name='"
				+ teacher.getName() + "'";
		return method.stUpdate(exe);
	}

	public int deleteType(Type type)
	{
		String exe = "delete from type where type_name='" + type.getName()
				+ "'";
		return method.stUpdate(exe);
	}

	public int addClass(Class_NIIT classNIIT)
	{
		String exe = "insert into class(teacher_id,classroom_id,major_id,type_id,date_start,description,isend)values(?,?,?,?,?,?,0)";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getTeacher().get(classNIIT.getTeacher()));
		param.add(common.getClassroom().get(classNIIT.getClassroom()));
		param.add(common.getMajor().get(classNIIT.getMajor()));
		param.add(common.getType().get(classNIIT.getType()));
		param.add(classNIIT.getDate_Start());
		param.add(classNIIT.getDescription());
		return method.pstUpdate(exe, param);
	}

	public void modifyDescription(Class_NIIT classNIIT, String description)
	{
		String exe = "update class set description='" + description + "' where id="+classNIIT.getId();
		method.stUpdate(exe);
	}

	public int deleteClass(Class_NIIT class_NIIT)
	{
		String exe="delete from class where id="+class_NIIT.getId();
		return method.stUpdate(exe);
	}
	
	public void modifyClass(Class_NIIT modifyClass)
	{
		String exe="update class set teacher_id=?,classroom_id=?,major_id=?,type_id=?,description=? where id=?";
		int teacherId=common.getTeacher().get(modifyClass.getTeacher());
		int classroom_id=common.getClassroom().get(modifyClass.getClassroom());
		int major_id=common.getMajor().get(modifyClass.getMajor());
		int type_id=common.getType().get(modifyClass.getType());
		String description=modifyClass.getDescription();
		int id=modifyClass.getId();
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(teacherId);
		param.add(classroom_id);
		param.add(major_id);
		param.add(type_id);
		param.add(description);
		param.add(id);
		method.pstUpdate(exe,param);
	}

	public int modifyClassId(Student student, int classId)
	{
		String exe="update student set class_id="+classId+" where id="+student.getId();
		return method.stUpdate(exe);
	}

	public int endClass(Class_NIIT class_NIIT)
	{
		String exe="update class set isend="+PublicMethod.out+" where id="+class_NIIT.getId();
		return method.stUpdate(exe);
	}
}
